# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
fs      = require 'fs'
uuid    = require 'uuid'
router = express.Router()
module.exports = router

router.all '/', (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    where_clause = "(
        p.uuid like $1 or
        p.acct_no like $1 or p.acct_name like $1 or p.notes like $1 or
        m1.merno like $1 or m1.name like $1 or t1.termid like $1 or
        m2.merno like $1 or m2.name like $1 or t2.termid like $1 or
        m3.merno like $1 or m3.name like $1
    )"
    # 如果没有复核权限，则只查询自己录入的未复核记录
    uncheck_clause = '1 = 1'
    if not res.locals.page_perms.check
        uncheck_clause = "p.inputer = '#{req.session.userinfo.uuid}'"

    # 如果没有提供查询关键字，则查询待复核记录
    await client.query "select count(*)::int as count
        from pay_p1 as p
        left join pay_merch as m1 on
            m1.uuid = json_extract_path_text(p.check_data, 'data', 't20_merid')
        left join pay_terms as t1 on
            t1.uuid = json_extract_path_text(p.check_data, 'data', 't20_termid')
        left join pay_merch as m2 on
            m2.uuid = json_extract_path_text(p.check_data, 'data', 'tz0_merid')
        left join pay_terms as t2 on
            t2.uuid = json_extract_path_text(p.check_data, 'data', 'tz0_termid')
        left join pay_merch as m3 on
            m3.uuid = json_extract_path_text(p.check_data, 'data', 'pay_merid')
        where #{ where_clause } and checked = false and #{uncheck_clause}", [
            '%' + res.locals.keyword + '%'
        ], defer err, result
    if err then done(client); return next(err)
    res.locals.total += result.rows[0].count

    await client.query "select count(*)::int as count
        from pay_p1 as p
        left join pay_merch as m1 on m1.uuid = p.t20_merid
        left join pay_terms as t1 on t1.uuid = p.t20_termid
        left join pay_merch as m2 on m2.uuid = p.tz0_merid
        left join pay_terms as t2 on t2.uuid = p.tz0_termid
        left join pay_merch as m3 on m3.uuid = p.pay_merid
        where #{ where_clause } and checked = true", [
            '%' + res.locals.keyword + '%'
        ], defer err, result
    if err then done(client); return next(err)
    res.locals.total += result.rows[0].count

    records = []

    # 如果没有提供查询关键字，则查询待复核记录
    await client.query "select p.*,
            json_extract_path_text(p.check_data, 'data', 'pay_chnl') as pay_chnl,
            json_extract_path_text(p.check_data, 'data', 'acct_no') as acct_no,
            json_extract_path_text(p.check_data, 'data', 'acct_name') as acct_name,
            json_extract_path_text(p.check_data, 'data', 'acct_cred_type') as acct_cred_type,
            json_extract_path_text(p.check_data, 'data', 'acct_cred_no') as acct_cred_no,
            json_extract_path_text(p.check_data, 'data', 'acct_bank_code') as acct_bank_code,
            json_extract_path_text(p.check_data, 'data', 'acct_bank_name') as acct_bank_name,
            json_extract_path_text(p.check_data, 'data', 'acct_mobile') as acct_mobile,
            json_extract_path_text(p.check_data, 'data', 'notes') as notes,
            m1.merno as t20_merno, m1.name as t20_mername,
            t1.termid as t20_termid,
            m2.merno as tz0_merno, m2.name as tz0_mername,
            t2.termid as tz0_termid,
            m3.merno as pay_merno, m3.name as pay_mername,
            u1.realname as input_user
        from pay_p1 as p
        left join pay_merch as m1 on
            m1.uuid = json_extract_path_text(p.check_data, 'data', 't20_merid')
        left join pay_terms as t1 on
            t1.uuid = json_extract_path_text(p.check_data, 'data', 't20_termid')
        left join pay_merch as m2 on
            m2.uuid = json_extract_path_text(p.check_data, 'data', 'tz0_merid')
        left join pay_terms as t2 on
            t2.uuid = json_extract_path_text(p.check_data, 'data', 'tz0_termid')
        left join pay_merch as m3 on
            m3.uuid = json_extract_path_text(p.check_data, 'data', 'pay_merid')
        left join web_users as u1 on u1.uuid = p.inputer
        where #{ where_clause } and checked = false and #{uncheck_clause}
            order by serial desc offset $2::int limit $3::int", [
            '%' + res.locals.keyword + '%'
            res.locals.offset
            res.locals.page_rows
        ], defer err, result
    if err then done(client); return next(err)
    records.push result.rows...

    await client.query "select p.*,
            m1.merno as t20_merno, m1.name as t20_mername,
            t1.termid as t20_termid,
            m2.merno as tz0_merno, m2.name as tz0_mername,
            t2.termid as tz0_termid,
            m3.merno as pay_merno, m3.name as pay_mername,
            u1.realname as input_user,
            u2.realname as check_user
        from pay_p1 as p
        left join pay_merch as m1 on m1.uuid = p.t20_merid
        left join pay_terms as t1 on t1.uuid = p.t20_termid
        left join pay_merch as m2 on m2.uuid = p.tz0_merid
        left join pay_terms as t2 on t2.uuid = p.tz0_termid
        left join pay_merch as m3 on m3.uuid = p.pay_merid
        left join web_users as u1 on u1.uuid = p.inputer
        left join web_users as u2 on u2.uuid = p.checker
        where #{ where_clause } and checked = true
            order by serial desc offset $2::int limit $3::int", [
            '%' + res.locals.keyword + '%'
            res.locals.offset
            res.locals.page_rows
        ], defer err, result
    if err then done(client); return next(err)
    records.push result.rows...
    done()
    res.render 'p1/index', records: records


body_dropdown_items = (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询消费终端
    await client.query \
        "select t.uuid, t.termid, t.shaded,
            m.inside as mer_inside, m.chnl as mer_chnl,
            m.name as mer_name, m.merno as mer_no
            from pay_terms as t
            left join pay_merch as m on m.uuid = t.merid
        where
            t.disabled = false and m.chnl = 1 and m.inside = false and
            json_extract_path_text(m.trctl, 'trcodes', 't20') = 'true'
        order by t.ctime desc", defer err, result
    if err then done(client); return next(err)
    res.locals.t20_terms = result.rows

    # 查询代付终端
    await client.query \
        "select t.uuid, t.termid, t.shaded,
            m.inside as mer_inside, m.chnl as mer_chnl,
            m.name as mer_name, m.merno as mer_no
            from pay_terms as t
            left join pay_merch as m on m.uuid = t.merid
        where
            t.disabled = false and m.chnl = 1 and m.inside = true and
            json_extract_path_text(m.trctl, 'trcodes', 'tz0') = 'true'
        order by t.ctime desc", defer err, result
    if err then done(client); return next(err)
    res.locals.tz0_terms = result.rows
    done()
    next()


# 检查身份证号码是否有效
id_validate = (id_no) ->
    coefficients = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2 ]
    cv = '10X98765432'
    sum = 0
    for v, i in coefficients
        sum += v * id_no[i]
    cv[sum % 11] == id_no[17]


check_submit = (req, res, next) ->
    for k in [
        't20_termid', 'tz0_termid', 'pay_chnl', 'pay_merid', 'acct_no'
    ]
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    # 去除账户名称空白字符
    if req.body.acct_name
        req.body.acct_name = req.body.acct_name.replace(/\s/g, '')

    # 校验收款账号身份证号码
    if req.body.acct_cred_type == '1'
        if req.body.acct_cred_no
            if req.body.acct_cred_no.length != 18
                return next new Error('收款账号开户人身份证号码长度不等于18')
            if not id_validate(req.body.acct_cred_no)
                return next new Error('收款账号开户人身份证号码校验失败')

    req.app.set 'notify_modified_time', moment().format('x')
    next()


router.get '/new', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '新增配置'
    res.render 'p1/new', record: {}


router.get '/new/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '新增配置'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select * from pay_p1 where uuid = $1", [
        req.params.id
    ], defer err, result
    if err then done(client); return next(err)
    done()

    record = result.rows[0]
    if not record or not record.check_data
        return next new Error('未查询到记录或记录缺少待复核数据，请检查.')
    record.check_data.data.serial = record.serial
    res.render 'p1/new', record: record.check_data.data


router.post '/new', check_submit, (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select merid from pay_terms where uuid = $1", [
        req.body.t20_termid
    ], defer err, result
    if err then done(client); return next(err)
    req.body.t20_merid = result.rows[0].merid

    await client.query "select merid from pay_terms where uuid = $1", [
        req.body.tz0_termid
    ], defer err, result
    if err then done(client); return next(err)
    req.body.tz0_merid = result.rows[0].merid

    # 将所有上传数据保存到待复核字段中
    check_data = action: 1, data: req.body
    check_data.acct_no_modified = true

    if req.body.uuid and req.body.uuid.length > 0
        await client.query \
            "update pay_p1 set check_data = $1, checked = false,
                inputer = $2, input_time = CURRENT_TIMESTAMP
                where uuid = $3 and inputer = $4", [
                JSON.stringify check_data
                req.session.userinfo.uuid
                req.body.uuid
                req.session.userinfo.uuid
            ], defer err, result
        if err then done(client); return next(err)
    else
        req.body.uuid = uuid.v4()
        await client.query \
            "insert into pay_p1 (
                uuid, inputer, check_data, t20_termid, tz0_termid
            ) values (
                $1, $2, $3, $4, $5
            )", [
                req.body.uuid,
                req.session.userinfo.uuid
                JSON.stringify check_data
                req.body.t20_termid
                req.body.tz0_termid
            ], defer err, result
        if err then done(client); return next(err)
    done()
    res.redirect '/p1'


router.post '/new/delete', (req, res, next) ->
    res.type 'json'
    for k in ['serial', 'uuid']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "delete from pay_p1 where uuid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/p1'


router.get '/edit/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '修改配置'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select * from pay_p1 where uuid = $1", [
        req.params.id
    ], defer err, result
    if err then done(client); return next(err)
    done()

    if not result.rows.length == 0
        return next new Error('未查询到记录，请检查.')

    if result.rows[0].checked
        record = result.rows[0]
    else
        record = result.rows[0].check_data.data
        record.serial = result.rows[0].serial
    res.render 'p1/edit', record: record


router.post '/edit', check_submit, (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select merid from pay_terms where uuid = $1", [
        req.body.t20_termid
    ], defer err, result
    if err then done(client); return next(err)
    req.body.t20_merid = result.rows[0].merid

    await client.query "select merid from pay_terms where uuid = $1", [
        req.body.tz0_termid
    ], defer err, result
    if err then done(client); return next(err)
    req.body.tz0_merid = result.rows[0].merid

    # 将所有上传数据保存到待复核字段中
    check_data = action: 2, data: req.body

    # 如果修改了账号，则复核时需要重新录入账号，这里作一个标记
    await client.query \
        "select count(*)::int as count from pay_p1
            where uuid = $1 and acct_no = $2", [
            req.body.uuid, req.body.acct_no
        ], defer err, result
    if err then done(client); return next(err)
    check_data.acct_no_modified = (result.rows[0].count == 0)

    await client.query \
        "update pay_p1 set
            check_data = $1, checked = false,
            inputer = $2, input_time = CURRENT_TIMESTAMP
        where uuid = $3", [
            JSON.stringify check_data
            req.session.userinfo.uuid
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.redirect '/p1'


router.get '/edit/delete/:id', (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "update pay_p1 set checked = true, check_data = null
            where uuid = $1", [
            req.params.id
        ], defer err, result
    if err then done(client); return next(err)
    done()

    req.app.set 'notify_modified_time', moment().format('x')
    res.redirect '/p1'


router.post '/delete', (req, res, next) ->
    res.type 'json'
    for k in ['serial', 'uuid']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 将记录数据保存到 check_data 中，后续主页需要查询 check_data 来展示信息
    await client.query \
        "select * from pay_p1 where uuid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    check_data = action : 3, data: result.rows[0]

    await client.query \
        "update pay_p1 set checked = false, check_data = $1,
            inputer = $2, input_time = CURRENT_TIMESTAMP
            where uuid = $3", [
            JSON.stringify check_data
            req.session.userinfo.uuid
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/p1'


router.get '/delete/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '撤销删除'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select * from pay_p1 where uuid = $1", [
        req.params.id
    ], defer err, result
    if err then done(client); return next(err)
    done()

    record = result.rows[0]
    if not record or not record.check_data
        return next new Error('未查询到记录或记录缺少待复核数据，请检查.')
    record.check_data.data.serial = record.serial
    res.render 'p1/delete', record: record.check_data.data


router.post '/delete/delete', (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "update pay_p1 set checked = true, check_data = null
            where uuid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    req.app.set 'notify_modified_time', moment().format('x')
    res.redirect '/p1'


router.get '/check/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '复核配置'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select p.*, u.realname as input_user
        from pay_p1 as p
        left join web_users as u on u.uuid = p.inputer
        where p.uuid = $1", [
        req.params.id
    ], defer err, result
    if err then done(client); return next(err)
    done()

    record = result.rows[0]
    if not record or not record.check_data
        return next new Error('未查询到记录或记录缺少待复核数据，请检查.')

    # 计算现有数据和原数据之间的差异
    if record.check_data.action == 2
        res.locals.diffs = []
        for name, value of record.check_data.data
            old_value = record[name] or ''
            if value.toString() isnt old_value.toString()
                res.locals.diffs.push name

    # 如果账号被修改过，则需要用户重新输入账号
    if record.check_data.acct_no_modified
        delete record.check_data.data.acct_no

    record.check_data.data.serial = record.serial
    record.check_data.data.action = record.check_data.action
    record.check_data.data.input_user = record.input_user
    record.check_data.data.input_time = record.input_time
    record.check_data.data._checking = true
    res.render 'p1/check', record: record.check_data.data


router.post '/check', check_submit, (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询待复核记录
    await client.query \
        "select check_data from pay_p1 where
            checked = false and inputer <> $1 and uuid = $2", [
            req.session.userinfo.uuid
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length == 0
        return next new Error('未查询到待复核记录，请检查.')

    check_data = result.rows[0].check_data

    # 删除时直接删除即可
    if check_data.action == 3
        await client.query \
            "delete from pay_p1 where
                checked = false and inputer <> $1 and uuid = $2", [
                req.session.userinfo.uuid
                req.body.uuid
            ], defer err, result
        if err then done(client); return next(err)
        done()
        return res.redirect '/p1'

    # 新增或修改时需要验证复核用户再次录入的账户
    if check_data.data.acct_no isnt req.body.acct_no
        return next new Error('您录入的收款账号与录入用户录入的不一致')

    await client.query \
        "update pay_p1 set usable = true, checked = true,
            checker = $1, check_time = CURRENT_TIMESTAMP,
            t20_merid = $2,     t20_termid = $3,
            tz0_merid = $4,     tz0_termid = $5,
            pay_chnl = $6,      pay_merid = $7,
            acct_no = $8,       acct_name = $9,
            acct_cred_no = $10, acct_cred_type = $11,
            acct_mobile = $12,  acct_bank_code = $13,
            acct_bank_name = $14,   notes = $15,
            check_data = null
        where
            checked = false and inputer <> $16 and uuid = $17", [
            req.session.userinfo.uuid
            check_data.data.t20_merid
            check_data.data.t20_termid
            check_data.data.tz0_merid
            check_data.data.tz0_termid
            check_data.data.pay_chnl
            check_data.data.pay_merid
            check_data.data.acct_no
            check_data.data.acct_name
            check_data.data.acct_cred_no
            parseInt(check_data.data.acct_cred_type)
            check_data.data.acct_mobile
            check_data.data.acct_bank_code
            check_data.data.acct_bank_name
            check_data.data.notes
            req.session.userinfo.uuid
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.redirect '/p1'


router.post '/pay_merids', (req, res, next) ->
    res.type 'json'
    for k in ['chnl']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select * from pay_merch where
        chnl = $1 and inside = false and
        disabled = false and shaded = false", [
            req.body.chnl
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.json succ: true, merids: result.rows
